import bcrypt
import pandas as pd

from datetime import datetime
from pyramid.security import remember, forget
from pyramid.view import view_config, forbidden_view_config
from pyramid.httpexceptions import HTTPFound
from pyramid.response import FileResponse

from .models import Student, Ftprecode, DBSession, TaxIncomeTable, TaxMonthIncomeTable
from .security import check_password


# 检查加密密码是否一致
def check_password(pw, hashed_pw):
    expected_hash = hashed_pw.encode('utf-8')
    return bcrypt.checkpw(pw.encode('utf-8'), expected_hash)


class MyproView:
    def __init__(self, request):
        self.request = request  # 存储传入的请求对象
        self.logged_in = request.authenticated_userid  # 标记用户是否已登录，存储认证的用户ID

    @view_config(route_name='index', renderer='./templates/index.html')
    def index(self):
        return dict(msg="Hello World")

    # ------------------------模板 -----------------------------
    @view_config(route_name='tables', renderer='./templates/tables.html')
    def tables(self):
        return dict(msg="Hello World")

    @view_config(route_name='tables_dynamic', renderer='./templates/tables_dynamic.html')
    def tables_dynamic(self):
        return dict(msg="Hello World")

    @view_config(route_name='general', renderer='./templates/general_elements.html')
    def general(self):
        return dict(msg="Hello World")

    @view_config(route_name='icons', renderer='./templates/icons.html')
    def icons(self):
        return dict(msg="Hello World")

    @view_config(route_name='chartjs', renderer='./templates/chartjs.html')
    def chartjs(self):
        return dict(msg="Hello World")

    @view_config(route_name='chartjs2', renderer='./templates/chartjs2.html')
    def chartjs2(self):
        return dict(msg="Hello World")

    @view_config(route_name='morisjs', renderer='./templates/morisjs.html')
    def morisjs(self):
        return dict(msg="Hello World")

    @view_config(route_name='echarts', renderer='./templates/echarts.html')
    def echarts(self):
        return dict(msg="Hello World")

    @view_config(route_name='other_charts', renderer='./templates/other_charts.html')
    def other_charts(self):
        return dict(msg="Hello World")

    @view_config(route_name='invoice', renderer='./templates/invoice.html')
    def invoice(self):
        return dict(msg="Hello World")

    @view_config(route_name='inbox', renderer='./templates/inbox.html')
    def invoice(self):
        return dict(msg="Hello World")

    # --------------------------------------------------------
    @view_config(route_name='login', renderer='./templates/login.html')
    @forbidden_view_config(renderer='./templates/login.html')
    def login(self):
        request = self.request  # 获取当前请求对象
        login_url = request.route_url('login')  # 获取登录页面的URL
        referrer = request.url  # 获取请求的来源URL
        if referrer == login_url:
            referrer = '/'  # 如果来源URL是登录页面本身，则将其改为根URL，避免循环登录
        came_from = request.params.get('came_from', referrer)  # 获取表单提交前的位置或默认为来源URL

        message = ''  # 初始化登录消息
        account = ''  # 初始化登录名
        pwd = ''  # 初始化密码
        print('\n\n\n')
        print(self.request.params)
        print('\n\n\n')
        if 'form.submitted' in request.params:  # 检查表单是否已提交
            # print('\n\n\n')
            # print(request.params)
            account = request.params['loginUser']  # 从请求中获取登录名
            pwd = (request.params['loginPassword'])  # 从请求中获取密码
            one = DBSession.query(Ftprecode).filter(Ftprecode.account == account).first()
            # print(one.pwd)

            # if hashed_pw and check_password(password, hashed_pw):
            if one and check_password(pwd, one.pwd):
                headers = remember(request, account)  # 设置记住登录的头部信息
                return HTTPFound(location="/first",
                                 headers=headers)  # 重定向回登录前的页面
            message = 'Failed login'  # 登录失败消息

        # 返回登录页面需要展示的数据
        return dict(
            name='Login',
            message=message,
            url=request.application_url + '/login',
            came_from=came_from,
            account=account,
            pwd=pwd,
        )

    @view_config(route_name='logout')
    def logout(self):
        request = self.request  # 获取当前请求对象
        headers = forget(request)  # 调用forget函数，生成用于忘记会话的头信息
        url = request.route_url('login')  # 根据路由名 'login' 生成回家页的URL
        # 返回一个重定向响应，前往home页面，并携带headers中的头信息
        return HTTPFound(location=url,
                         headers=headers)

    @view_config(route_name='tax', renderer='./templates/tax.html')
    def tax(self):
        tax_income_table = DBSession.query(TaxIncomeTable).all()

        # 获取当前日期和时间
        now = datetime.now()

        # 获取当前的年、月、日
        year = now.year
        month = now.month
        day = now.day
        date = f"{year}年{month}月{day}日 "
        return dict(date=date, tax_income_table=tax_income_table)

    @view_config(route_name='tax_month', renderer='./templates/tax_month.html')
    def tax_month(self):
        tax_month_income_table = DBSession.query(TaxMonthIncomeTable).all()

        # 获取当前日期和时间
        now = datetime.now()

        # 获取当前的年、月、日
        year = now.year
        month = now.month
        day = now.day
        date = f"{year}年{month}月{day}日 "
        return dict(date=date, tax_month_income_table=tax_month_income_table)

    @view_config(route_name='个税总表', renderer='json')
    def 个税总表(self):
        sql = f"select * from tax_income_table where version > 1;"
        df = pd.read_sql_query(sql, DBSession.bind)
        df['dtime'] = pd.to_datetime(df['dtime'], unit='s')
        df['create_time'] = pd.to_datetime(df['create_time'], unit='s')

        df.rename(columns={
            'id': '序号',
            'addr': '属地',
            'id_card': '身份证号',
            'name': '姓名',
            'calendar_month': '自然月',
            'cumulative_taxincome': '最新累计应纳税所得额',
            'cumulative_income': '最新累计应发额',
            'cumulative_tax': '最新累计个税',
            'entry_date': '入职日期',
            'dtime': '数据时间',
            'create_time': '创建时间',
            'version': '版本号'}, inplace=True)
        df = df.drop(columns=['cidsum'])

        # 将 DataFrame 写入 Excel 文件
        excel_filename = '个税总表.xlsx'
        with pd.ExcelWriter(excel_filename, engine='openpyxl') as writer:
            df.to_excel(writer, index=False)

        # 创建 FileResponse 实例
        return FileResponse(excel_filename, request=self.request,
                            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

    @view_config(route_name='个税分表', renderer='json')
    def 个税分表(self):
        sql = f"select * from tax_month_income_table;"
        df = pd.read_sql_query(sql, DBSession.bind)
        df['dtime'] = pd.to_datetime(df['dtime'], unit='s')

        df.rename(columns={
            'id': '序号',
            'classes': '人员分类',
            'addr': '属地',
            'id_card': '身份证号',
            'name': '姓名',
            'calendar_month': '自然月',
            'filename': '文件名',
            'is_first': '是否扣除5000',
            'month_taxincome': '当月应纳税所得额',
            'month_income': '当月应发合计',
            'month_tax': '当月个税',
            'entry_date': '入职时间',
            'dtime': '数据时间',
            'operations_personnel': '所属运营',
            'isgrant': '是否发放'
        }, inplace=True)
        df['是否发放'].replace(0, '否', inplace=True)
        df['是否发放'].replace(1, '是', inplace=True)

        # 将 DataFrame 写入 Excel 文件
        excel_filename = '个税分表.xlsx'
        with pd.ExcelWriter(excel_filename, engine='openpyxl') as writer:
            df.to_excel(writer, index=False)

        # 创建 FileResponse 实例
        return FileResponse(excel_filename, request=self.request,
                            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
